package com.centling.dao.impl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.centling.core.BaseDAO;
import com.centling.dao.IUtilsDAO;

@Repository
@Transactional
public class UtilsDAO extends BaseDAO implements IUtilsDAO {

	@SuppressWarnings("rawtypes")
	public Integer getCityIDByCityName(String cityName){
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select FID from CRM_City where FName=:FName ");
		List list = session.createSQLQuery(hql.toString()).setParameter("FName", cityName).list();
		if (list!=null&&!list.isEmpty()){
			return Integer.valueOf(list.get(0).toString());
		}
		return null;
	}

	@SuppressWarnings("rawtypes")
	public Integer getICMaxNum(String fTableName) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("SELECT FMaxNum FROM ICMaxNum where FTableName=:FTableName ");
		Query query = session.createSQLQuery(hql.toString()).setParameter("FTableName", fTableName);
		List list = query.list();
		if (list!=null&&!list.isEmpty()){
			return Integer.valueOf(list.get(0).toString())+1;
		}
		return -1;
	}
	
	@Transactional
	public void setICMaxNum(String fTableName) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("update ICMaxNum set FMaxNum=FMaxNum+1 where FTableName=:FTableName ");
		session.createSQLQuery(hql.toString()).setParameter("FTableName", fTableName).executeUpdate();
	}

	@SuppressWarnings("rawtypes")
	public String getFExchangeRateByFCurrencyID(Integer fCurrencyID, String datestr) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("SELECT t_ExchangeRateEntry.FExchangeRate ");
		hql.append("FROM t_ExchangeRate  INNER JOIN t_ExchangeRateEntry  ON t_ExchangeRate.FID=t_ExchangeRateEntry.FID ");
		hql.append("	LEFT JOIN t_Currency  ON t_ExchangeRateEntry.FCyTo=t_Currency.FCurrencyID AND t_Currency.FCurrencyID<>0 ");
		hql.append("	LEFT JOIN v_BASE_ExRateType  ON t_ExchangeRateEntry.FExchangeRateType=v_BASE_ExRateType.FID ");
		hql.append("	LEFT JOIN t_User  ON t_ExchangeRateEntry.FChkUserID=t_User.FUserID AND t_User.FUserID<>0 ");
		hql.append("WHERE t_ExchangeRate.FID in ( ");
		hql.append("		SELECT t_ExchangeRate.FID ");
		hql.append(" 		FROM t_ExchangeRate  INNER JOIN t_ExchangeRateEntry  ON t_ExchangeRate.FID=t_ExchangeRateEntry.FID ");
		hql.append("			LEFT JOIN t_Currency  ON t_ExchangeRateEntry.FCyTo=t_Currency.FCurrencyID AND t_Currency.FCurrencyID<>0 ");
		hql.append("			LEFT JOIN v_BASE_ExRateType  ON t_ExchangeRateEntry.FExchangeRateType=v_BASE_ExRateType.FID ");
		hql.append("			LEFT JOIN t_User  ON t_ExchangeRateEntry.FChkUserID=t_User.FUserID AND t_User.FUserID<>0 ");
		hql.append("		WHERE t_ExchangeRate.FParentID=1 AND t_ExchangeRate.FDetail<>0 AND t_ExchangeRate.FIsNativeRate=0 AND t_ExchangeRateEntry.FEndDate>=:FEndDate1 ");
		hql.append("			AND t_ExchangeRateEntry.FCyTo IN (1,1000,1001) AND t_ExchangeRate.FClassTypeID=1014002 AND t_ExchangeRate.FDiscontinued=0 ");
		hql.append("	) and t_ExchangeRate.FParentID=1 AND t_ExchangeRate.FDetail<>0 AND t_ExchangeRate.FIsNativeRate=0   AND t_ExchangeRateEntry.FEndDate>=:FEndDate2 ");
		hql.append(" 	AND t_ExchangeRateEntry.FCyTo IN (1,1000,1001) AND t_ExchangeRate.FClassTypeID=1014002 AND t_ExchangeRate.FDiscontinued=0");
		hql.append(" 	and t_Currency.FName in (SELECT FName FROM t_Currency t1 WHERE t1.FDeleted=0 AND t1.FCurrencyID=:FCurrencyID)");
		Query query = session.createSQLQuery(hql.toString())
				.setParameter("FEndDate1", datestr)
				.setParameter("FEndDate2", datestr)
				.setParameter("FCurrencyID", fCurrencyID);
		List list = query.list();
		if (list!=null&&!list.isEmpty()){
			return list.get(0).toString();
		}
		return null;
	}

	public void processT_itemWhenInsertCustomer(Integer fItemID, String fNumber, String fName) {
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("insert into t_Item (FItemID,FItemClassID,FExternID,FNumber,FParentID,FLevel,FDetail,FName,FUnUsed,FBrNo,FFullNumber,FDiff,");
		hql.append("	FDeleted,FShortNumber,FFullName,UUID,FGRCommonID,FSystemType,FUseSign,FAccessory,FGrControl) ");
		hql.append("values(:FItemID,1,-1,:FNumber,0,1,1,:FName,0,'0',:FFullNumber,0,0,:FShortNumber,:FFullName,newid(),-1,1,0,0,-1); ");
		Query query = session.createSQLQuery(hql.toString());
		
		query.setParameter("FItemID", fItemID);
		query.setParameter("FNumber", fNumber);
		query.setParameter("FName", fName);
		query.setParameter("FFullNumber", fNumber);
		query.setParameter("FShortNumber", fNumber);
		query.setParameter("FFullName", fName);
		
		query.executeUpdate();
	}
	
	/*@SuppressWarnings("rawtypes")
	@Transactional
	public String getFNumberBeforeSaveCustomer() {
		String returnVal = "";
		//1
		Session session = this.sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("Update t_BillCodeRule Set FReChar = FReChar Where FBillTypeID ='1012000' ");
		session.createSQLQuery(hql.toString()).executeUpdate();
		
		Integer	curNo = null;	//当前游标
		String	curFNumber = "";//当前的客户编号
		hql = new StringBuffer();
		hql.append("select FProjectVal from t_BillCodeRule where fbilltypeid='1012000' and fprojectid=3 ");
		Query query = session.createSQLQuery(hql.toString());
		List list = query.list();
		if (list!=null&&!list.isEmpty()){
			curNo = Integer.valueOf(list.get(0).toString());
		} else return null;
		
		if (curNo!=null){
			hql = new StringBuffer();
			hql.append("select FProjectVal from t_BillCodeRule where fbilltypeid='1012000' and fprojectid=1 ");
			query = session.createSQLQuery(hql.toString());
			list = query.list();
			if (list!=null&&!list.isEmpty()){
				curFNumber = list.get(0).toString();
			} else return null;
			if (curFNumber!=null){
				curFNumber += LocalUtils.getSetLengthStr(curNo.toString(), 8);
			} else return null;
		} else return null;
		
		//2 
		hql = new StringBuffer();
		hql.append("if exists(select 1 from t_Organization with(nolock) where FNumber =:curFNumber) ");
		hql.append("	begin ");
		hql.append("		update t_billcoderule");
		hql.append("		set fprojectval=fprojectval+1");
		hql.append("		where fbilltypeid='1012000' and fprojectid=3");
		hql.append("		select 0 ");
		hql.append("	end");
		hql.append("else");
		hql.append("	select 1");
		session.createSQLQuery(hql.toString()).setParameter("curFNumber", curFNumber).executeUpdate();
		
		//3
		hql = new StringBuffer();
		hql.append("DECLARE @FID INT ");
		hql.append("DECLARE @FPrlVal varchar(80) ");
		hql.append("SELECT @FID=FID,@FPrlVal=fprojectval+1 ");
		hql.append("FROM t_BillCodeRule WITH(nolock) ");
		hql.append("WHERE fbilltypeid='1012000' and fprojectid=3 ");
		hql.append("update t_billcoderule ");
		hql.append("set fprojectval=@FPrlVal,flength= ");
		hql.append("case when (flength-len(@FPrlVal))>=0 ");
		hql.append("	then flength else len(@FPrlVal)  ");
		hql.append("	end ");
		hql.append("where FID=@FID ");
		hql.append("Update ICBillNo ");
		hql.append("Set FCurNo=isnull(@FPrlVal,1) ");
		hql.append("where fbillid ='1012000' ");
		session.createSQLQuery(hql.toString()).executeUpdate();
		
		//4
		String fcurno = "";
		hql = new StringBuffer();
		hql.append("select FCurNo from ICBillNo where fbillid ='1012000' ");
		query = session.createSQLQuery(hql.toString());
		list = query.list();
		if (list!=null&&!list.isEmpty()){
			fcurno = list.get(0).toString();
		} else return null;
		hql = new StringBuffer();
		hql.append("Update ICBillNo ");	
		hql.append("Set FDesc = :FDesc ");	
		hql.append("Where FBillID ='1012000' ");	
		session.createSQLQuery(hql.toString()).setParameter("FDesc", "CUST+"+LocalUtils.getSetLengthStr(fcurno, 8)).executeUpdate();
		
		returnVal = "CUST"+LocalUtils.getSetLengthStr(String.valueOf((Integer.valueOf(fcurno)-1)), 8);
		
		// 5
		hql = new StringBuffer();
		hql.append("declare @p2 int ");	
		hql.append("set @p2=:p2 ");	
		hql.append("exec GetICMaxNum @TableName=N't_Item',@FInterID=@p2 output,@Increment=1,@UserID=16394 ");
		hql.append("select @p2 ");	
		session.createSQLQuery(hql.toString()).setParameter("p2", Integer.valueOf(fcurno)).executeUpdate();
		return returnVal;
	}*/

}
